<?php
class Projects {

	private function getRankArray() {
		return array('k' => 'kingdom', 'p' => 'phylum', 'c' => 'class', 'o' => 'order', 'f' => 'family', 'g' => 'genus', 's' => 'scientificName');
	}
	private function getTermArray() {
		return array('k' => 'kingdom_o', 'p' => 'phylum_o', 'c' => 'class_o', 'o' => 'order_o', 'f' => 'family_o', 'g' => 'genus_o', 's' => 'scientificName_o');
	}
	private function getFieldsArray() {
		return array('scientificNameAuthorship', 'scientificName', 'genus', 'family', 'order', 'class', 'phylum', 'kingdom');
	}
	public function __construct() {}
	public function getError($errorCode) {
		$errorsArray = array(
			  101 => 'Zip File does not exist'
			, 102 => 'Failed to Open Zip File.'
			, 103 => 'Core File does not exist.'
			, 104 => 'Invalid Project Code.'
		);
		return $errorsArray[$errorCode];
	}

	public function get($property) {
		return (isset($this->{$property})) ? $this->{$property} : false;
	}

	public function set($property,$value) {
		$this->{$property} = $value;
		return true;
	}

	public function projectExists($dbName,$projectId) {
		$query = sprintf("SELECT COUNT(*) ct FROM information_schema.tables WHERE table_schema = '%s' AND table_name = '%s'", mysql_escape_string($dbName), mysql_escape_string($projectId));
		$ret = $this->db->query_one($query);
		if($ret->ct > 0) {
			return true;
		} else {
			return false;
		}
	}

/**
 * Loads the Project by project id
 * @param int $projectId
 */
	public function loadByProjectId($projectId,$detailFlag = false) {
		$query = sprintf("SELECT * FROM `projects` WHERE BINARY `projectId` = '%s' ;", mysql_escape_string($projectId));
		$row = $this->db->query_one($query);
		if(!is_null($row)) {
			foreach($row as $property => $value) {
				$this->set($property, $value);
			}

			if($detailFlag  && in_array($this->get('stage'), array('CLASSIFYING'))) {
				# Test to see if table has been created first.
#				if( mysql_num_rows( mysql_query("SHOW TABLES LIKE '" . $projectId . "'"))) {
					# Table is found.
					$qry = sprintf(" SELECT count(*) ct FROM `%s`;", mysql_escape_string($projectId));
					$rt = $this->db->query_one($qry);
					$this->set('y',$rt->ct);
					$qry = sprintf(" SELECT count(*) ct FROM `%s` WHERE `classified` = 1;", mysql_escape_string($projectId));
					$rt = $this->db->query_one($qry);
					$this->set('x',$rt->ct);
#				} else {
#					return false;
#				}
			}

			return true;
		}
		return false;
	}

	public function loadById($id) {
		$query = sprintf("SELECT * FROM `projects` WHERE `autoId` = '%s' ;", mysql_escape_string($id));
		$row = $this->db->query_one($query);
		if(!is_null($row)) {
			foreach($row as $property => $value) {
				$this->set($property, $value);
			}
			return true;
		}
		return false;
	}

/**
 * Call to this function should be preceeded by a call to the load by id / projectId function
 */
	public function getQuickMode() {
		$query = sprintf(" SELECT count(*) ct FROM `%s` WHERE `family_o` != 'NULL' AND `family_o` != '' ", mysql_escape_string($this->get('projectId')));
		$ret = $this->db->query_one($query);
		if($ret->ct > 0) {
			return true;
		} else {
			return false;
		}
	}

	public function getByFilter($filter,$detailFlag = true) {
		global $config;
		$where = buildWhere($filter['filter']);
		if ($where != '') {
			$where = " WHERE " . $where;
		}
		$where .= build_order( $filter['order'] );
		$where .= build_limit($filter['start'], $filter['limit']);
		$query = 'SELECT SQL_CALC_FOUND_ROWS `projectId`,`stage`,`dateAdded`,`ip`,`url`,`quickMode`,`setAuthor`,`setTaxa` FROM `projects` ' . $where;
		$projects = $this->db->query_all($query);
		$this->set('recordCount',$this->db->query_total());
		if($detailFlag) {
			if(is_array($projects) && count($projects)) {
				foreach($projects as &$project) {
					if(in_array($project->stage, array('NEW','ERROR'))) continue;
					$qry = sprintf(" SELECT count(*) ct FROM `%s` ",$project->projectId);
					$rt = $this->db->query_one($qry);
					$project->recordCount = $rt->ct;
					$project->diskSize = getDirectorySize($config['paths']['project'] . $project->projectId . '/');
				}
			}
		}
		return $projects;
	}

	public function getQueuedProjectCount() {
		$query = sprintf("SELECT count(*) ct FROM `projects` WHERE `stage` = 'CLASSIFYING' AND `projectId` < '%s';", $this->get('projectId'));
		$ret = $this->db->query_one($query);
		return $ret->ct;
	}
	
	public function getCleanUpRecords($days = 7) {
		$query = sprintf(" SELECT `projectId` FROM `projects` WHERE datediff(curdate(),`dateAdded`) > %d ", $days);
		return($this->db->query($query));
	}

	public function deleteProject($projectId,$delFlag = true) {
		if($delFlag) {
			$query = sprintf(" DROP TABLE IF EXISTS `%s`;", mysql_escape_string($projectId));
			$this->db->query($query);
		}
		$query = sprintf(" DELETE FROM `projects` WHERE `projectId` = '%s' ", mysql_escape_string($projectId));
		return($this->db->query($query));
	}

/**
* Saves the data to the db
*/
	public function save() {
		if($this->fieldExists($this->get('projectId'))) {
			$query = sprintf("UPDATE `projects` SET `url` = '%s',`ip` = '%s',`stage` = '%s',`classificationId` = '%s',`exported` = '%s', `pAuth` = '%s', `pAuthText` = '%s', `sAuth` = '%s', `sAuthText` = '%s', `quickMode` = '%s', `setAuthor` = '%s', `setTaxa` = '%s', `dateAdded` = '%s',`dateModified` = NOW(), `comments` = '%s', `scientificName` = '%s', `scientificName_variance` = '%s', `genus` = '%s', `genus_variance` = '%s', `family` = '%s', `family_variance` = '%s', `order` = '%s', `order_variance` = '%s', `class` = '%s', `class_variance` = '%s', `phylum` = '%s', `phylum_variance` = '%s', `kingdom` = '%s', `kingdom_variance` = '%s' WHERE `projectId` = '%s';"
			, mysql_escape_string($this->get('url'))
			, mysql_escape_string($this->get('ip'))
			, mysql_escape_string($this->get('stage'))
			, mysql_escape_string($this->get('classificationId'))
			, mysql_escape_string($this->get('exported'))
			, mysql_escape_string($this->get('pAuth'))
			, mysql_escape_string($this->get('pAuthText'))
			, mysql_escape_string($this->get('sAuth'))
			, mysql_escape_string($this->get('sAuthText'))
			, mysql_escape_string($this->get('quickMode'))
			, mysql_escape_string($this->get('setAuthor'))
			, mysql_escape_string($this->get('setTaxa'))
			, mysql_escape_string($this->get('dateAdded'))
			, mysql_escape_string($this->get('comments'))
			, mysql_escape_string($this->get('scientificName'))
			, mysql_escape_string($this->get('scientificName_variance'))
			, mysql_escape_string($this->get('genus'))
			, mysql_escape_string($this->get('genus_variance'))
			, mysql_escape_string($this->get('family'))
			, mysql_escape_string($this->get('family_variance'))
			, mysql_escape_string($this->get('order'))
			, mysql_escape_string($this->get('order_variance'))
			, mysql_escape_string($this->get('class'))
			, mysql_escape_string($this->get('class_variance'))
			, mysql_escape_string($this->get('phylum'))
			, mysql_escape_string($this->get('phylum_variance'))
			, mysql_escape_string($this->get('kingdom'))
			, mysql_escape_string($this->get('kingdom_variance'))
			, mysql_escape_string($this->get('projectId'))
			);
		} else {
			$query = sprintf("INSERT INTO `projects` SET `projectId` = '%s', `url` = '%s',`ip` = '%s',`stage` = 'NEW',`classificationId` = '%s',`exported` = '%s', `pAuth` = '%s', `pAuthText` = '%s', `sAuth` = '%s', `sAuthText` = '%s', `quickMode` = '%s', `setAuthor` = '%s', `setTaxa` = '%s', `dateAdded` = NOW(),`dateModified` = NOW(), `comments` = '%s', `scientificName` = '%s', `scientificName_variance` = '%s', `genus` = '%s', `genus_variance` = '%s', `family` = '%s', `family_variance` = '%s', `order` = '%s', `order_variance` = '%s', `class` = '%s', `class_variance` = '%s', `phylum` = '%s', `phylum_variance` = '%s', `kingdom` = '%s', `kingdom_variance` = '%s';"
			, $this->genPass()
			, mysql_escape_string($this->get('url'))
			, mysql_escape_string($this->get('ip'))
			, mysql_escape_string($this->get('classificationId'))
			, mysql_escape_string($this->get('exported'))
			, mysql_escape_string($this->get('pAuth'))
			, mysql_escape_string($this->get('pAuthText'))
			, mysql_escape_string($this->get('sAuth'))
			, mysql_escape_string($this->get('sAuthText'))
			, mysql_escape_string($this->get('quickMode'))
			, mysql_escape_string($this->get('setAuthor'))
			, mysql_escape_string($this->get('setTaxa'))
			, mysql_escape_string($this->get('comments'))
			, mysql_escape_string($this->get('scientificName'))
			, mysql_escape_string($this->get('scientificName_variance'))
			, mysql_escape_string($this->get('genus'))
			, mysql_escape_string($this->get('genus_variance'))
			, mysql_escape_string($this->get('family'))
			, mysql_escape_string($this->get('family_variance'))
			, mysql_escape_string($this->get('order'))
			, mysql_escape_string($this->get('order_variance'))
			, mysql_escape_string($this->get('class'))
			, mysql_escape_string($this->get('class_variance'))
			, mysql_escape_string($this->get('phylum'))
			, mysql_escape_string($this->get('phylum_variance'))
			, mysql_escape_string($this->get('kingdom'))
			, mysql_escape_string($this->get('kingdom_variance'))
			);
		}

		if($this->db->query($query)) {
			return(true);
		} else {
			return (false);
		}
	}
	/**
	*checks whether field exists in database
	*/
	public function fieldExists ($projectId){

		$query = sprintf("SELECT * FROM `projects` WHERE `projectId` = '%s';",  mysql_escape_string($projectId));
		$ret = $this->db->query_one( $query );
		if ($ret == NULL) {
			return false;
		} else {
			return true;
		}
	}

	public function make_seed() {
	list($usec, $sec) = explode(' ', microtime());
	return (float) $sec + ((float) $usec * 100000);
	}
	
	public function genPass() {
		$password_length = 9;
		srand($this->make_seed());
		$alfa = "1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM";
		$token = "";
		for($i = 0; $i < $password_length; $i ++) {
		$token .= $alfa[rand(0, strlen($alfa))];
		}
		return $token;
	}

# project table functions

	public function getTotalRecordCount($projectId) {
		if($projectId == '') return false;
		$query = sprintf(" SELECT count(*) ct FROM `%s` ", mysql_escape_string($projectId));
		$rt = $this->db->query_one($query);
		return $rt->ct;
	}

	public function getByTableFilter($filter,$flag = true) {
		if($filter['projectId'] == '') return false;
		$where = buildWhere($filter['filter']);
		if ($where != '') {
			$where = " WHERE " . $where;
		}

		$groupTerm = (in_array(trim($filter['stage']),array('LOADED','CLASSIFYING'))) ? ' GROUP BY `scientificName_o`, genus_o, family_o ' : ' GROUP BY `scientificName_p`, genus_p, family_p ';

		if (in_array(trim($filter['stage']),array('LOADED','CLASSIFYING'))) {
			$groupTerm = ' GROUP BY `scientificName_o`, genus_o, family_o ';
		} else {
			$qry = sprintf(" SELECT count(`scientificName_p`) ct FROM `%s` WHERE `scientificName_p` != 'NULL' AND `scientificName_p` != '' ", mysql_escape_string($filter['projectId']));
			$rt = $this->db->query_one($qry);
			$groupTerm = ($rt->ct > 1) ? ' GROUP BY `scientificName_p`, genus_p, family_p ' : ' GROUP BY `scientificName_o`, genus_o, family_o ';
		}

		$filter['field'] = str_replace(array('_p','_o'),'',$filter['field']);
		if(in_array($filter['field'],$this->getfieldsArray()) && $filter['query'] != '') {
			switch($filter['searchType']) {
				case 'anymatch':
					$where .= sprintf(" AND ( `%s_o` LIKE '%%%s%%' OR  `%s_p` LIKE '%%%s%%' ) ",$filter['field'],mysql_escape_string($filter['query']),$filter['field'],mysql_escape_string($filter['query']));
					break;
				case 'endswith':
					$where .= sprintf(" AND ( `%s_o` LIKE '%%%s' OR `%s_p` LIKE '%%%s' ) ",$filter['field'],mysql_escape_string($filter['query']),$filter['field'],mysql_escape_string($filter['query']));
					break;
				case 'exactmatch':
					$where .= sprintf(" AND ( `%s_o` = '%s' OR `%s_p` = '%s' ) ",$filter['field'],mysql_escape_string($filter['query']),$filter['field'],mysql_escape_string($filter['query']));
					break;
				default:
				case 'startswith':
					$where .= sprintf(" AND ( `%s_o` LIKE '%s%%' OR `%s_p` LIKE '%s%%' ) ",$filter['field'],mysql_escape_string($filter['query']),$filter['field'],mysql_escape_string($filter['query']));
					break;
			}
		}
		$query = sprintf(" SELECT SQL_CALC_FOUND_ROWS * FROM `%s` ",mysql_escape_string($filter['projectId'])) . $where . $groupTerm;
		
		$query .= build_order( $filter['order'] );
		$query .= build_limit($filter['start'], $filter['limit']);
		if($flag) {
			return($this->db->query_all($query));
		} else {
			return($this->db->query($query));
		}
	}

	public function getReMapData() {
		if($this->get('projectId') == '') return false;
		$query = sprintf(" SELECT `kingdom_p` `kingdom`, `phylum_p` `phylum`, `class_p` `class`, `order_p` `order`, `family_p` `family`, `genus_p` `genus`, `scientificName_p` `scientificName`, `scientificNameAuthorship_p` `scientificNameAuthorship`, `taxonAccordingTo` FROM `%s` ORDER BY `recordId` ASC ", $this->get('projectId'));
		return($this->db->query($query));
	}


	public function clearProcessedData() {
		$this->set('kingdom_p', '');
		$this->set('phylum_p', '');
		$this->set('class_p', '');
		$this->set('order_p', '');
		$this->set('family_p', '');
		$this->set('genus_p', '');
		$this->set('scientificName_p', '');
		$this->set('scientificNameAuthorship_p', '');
	}
	
	public function loadByRecord($row) {
		
		if(!is_null($row)) {
			foreach($row as $property => $value) {
				$this->set($property, $value);
			}
			return true;
		}
		return false;
	}

	public function loadByProjectTableId($projectId,$recordId) {
		$query = sprintf("SELECT * FROM `%s` WHERE `recordId` = '%s' ;", mysql_escape_string($projectId), mysql_escape_string($recordId));
		$row = $this->db->query_one($query);
		if(!is_null($row)) {
			foreach($row as $property => $value) {
				$this->set($property, $value);
			}
			return true;
		}
		return false;
	}

	public function saveProjectTable($projectId) {
		if($projectId == '') {return false;}
		if($this->fieldTableExists($projectId,$this->get('recordId'))) {
			$query = sprintf("UPDATE `%s` SET `kingdom_o` = '%s', `kingdom_p` = '%s', `phylum_o` = '%s', `phylum_p` = '%s', `class_o` = '%s', `class_p` = '%s', `order_o` = '%s', `order_p` = '%s', `family_o` = '%s', `family_p` = '%s', `genus_o` = '%s', `genus_p` = '%s', `scientificName_o` = '%s', `scientificName_p` = '%s', `scientificNameAuthorship_o` = '%s', `scientificNameAuthorship_p` = '%s' WHERE `recordId` = '%s';"
			, mysql_escape_string($projectId)
			, mysql_escape_string($this->get('kingdom_o'))
			, mysql_escape_string($this->get('kingdom_p'))
			, mysql_escape_string($this->get('phylum_o'))
			, mysql_escape_string($this->get('phylum_p'))
			, mysql_escape_string($this->get('class_o'))
			, mysql_escape_string($this->get('class_p'))
			, mysql_escape_string($this->get('order_o'))
			, mysql_escape_string($this->get('order_p'))
			, mysql_escape_string($this->get('family_o'))
			, mysql_escape_string($this->get('family_p'))
			, mysql_escape_string($this->get('genus_o'))
			, mysql_escape_string($this->get('genus_p'))
			, mysql_escape_string($this->get('scientificName_o'))
			, mysql_escape_string($this->get('scientificName_p'))
			, mysql_escape_string($this->get('scientificNameAuthorship_o'))
			, mysql_escape_string($this->get('scientificNameAuthorship_p'))
			, mysql_escape_string($this->get('recordId'))
			);
		} else {
			$query = sprintf("INSERT INTO `%s` SET `kingdom_o` = '%s', `kingdom_p` = '%s', `phylum_o` = '%s', `phylum_p` = '%s', `class_o` = '%s', `class_p` = '%s', `order_o` = '%s', `order_p` = '%s', `family_o` = '%s', `family_p` = '%s', `genus_o` = '%s', `genus_p` = '%s', `scientificName_o` = '%s', `scientificName_p` = '%s', `scientificNameAuthorship_o` = '%s', `scientificNameAuthorship_p` = '%s';"
			, mysql_escape_string($projectId)
			, mysql_escape_string($this->get('kingdom_o'))
			, mysql_escape_string($this->get('kingdom_p'))
			, mysql_escape_string($this->get('phylum_o'))
			, mysql_escape_string($this->get('phylum_p'))
			, mysql_escape_string($this->get('class_o'))
			, mysql_escape_string($this->get('class_p'))
			, mysql_escape_string($this->get('order_o'))
			, mysql_escape_string($this->get('order_p'))
			, mysql_escape_string($this->get('family_o'))
			, mysql_escape_string($this->get('family_p'))
			, mysql_escape_string($this->get('genus_o'))
			, mysql_escape_string($this->get('genus_p'))
			, mysql_escape_string($this->get('scientificName_o'))
			, mysql_escape_string($this->get('scientificName_p'))
			, mysql_escape_string($this->get('scientificNameAuthorship_o'))
			, mysql_escape_string($this->get('scientificNameAuthorship_p'))
			);
		}
		if($this->db->query($query)) {
			return(true);
		} else {
			return (false);
		}
	}

	public function resetClassifyFlag($projectId) {
		if(!$this->loadByProjectId($projectId)) return false;
		$query = sprintf(" UPDATE `%s` SET `classified` = 0;",mysql_escape_string($projectId));
		return $this->db->query($query);
	}

	public function saveDynamicProjectTable($projectId, $term, $value) {
		if($projectId == '') {return false;}
#		if(!in_array($term,$this->getTermArray())) {return false;}

		foreach($this->getRankArray() as $trm) {
			$trmo = $trm . '_o';
			$trmp = $trm . '_p';
			$ar[] = sprintf(" `$trmp` = '%s' ",mysql_escape_string($this->get($trmp)));

			if($trmo == $term && $trmo != 'scientificName_o') break;
		}

		$ar[] = sprintf(" `scientificNameAuthorship_p` = '%s' ", mysql_escape_string($this->get('scientificNameAuthorship_p')));
		$ar[] = sprintf(" `classified` = '%s' ",mysql_escape_string($this->get('classified')));
		$ar[] = sprintf(" `taxonAccordingTo` = '%s' ",mysql_escape_string($this->get('taxonAccordingTo')));

		$query = sprintf("UPDATE `%s` SET %s WHERE `%s` = '%s';"
			, mysql_escape_string($projectId)
			, implode(',',$ar)
			, mysql_escape_string($term)
			, mysql_escape_string($value)
		);

		if($this->db->query($query)) {
			return(true);
		} else {
			return (false);
		}
	}

	public function saveAuthorship($projectId,$value) {
		if($projectId == '') {return false;}
		$ar[] = sprintf(" `scientificNameAuthorship_p` = '%s' ", mysql_escape_string($this->get('scientificNameAuthorship_p')));
		if($this->get('taxonAccordingTo') != '') {
			$ar[] = sprintf(" `taxonAccordingTo` = '%s' ", mysql_escape_string($this->get('taxonAccordingTo')));
		}
		$ar[] = sprintf(" `classified` = '%s' ",mysql_escape_string($this->get('classified')));

		$query = sprintf("UPDATE `%s` SET %s WHERE `scientificName_o` = '%s';"
			, mysql_escape_string($projectId)
			, implode(',',$ar)
			, mysql_escape_string($value)
		);

		if($this->db->query($query)) {
			return(true);
		} else {
			return (false);
		}
	}

	/**
	*checks whether field exists in database
	*/
	public function fieldTableExists ($projectId,$recordId){
		$query = sprintf("SELECT * FROM `%s` WHERE `recordId` = '%s';", mysql_escape_string($projectId), mysql_escape_string($recordId));
		$ret = $this->db->query_one( $query );
		if ($ret == NULL) {
			return false;
		} else {
			return true;
		}
	}


}
?>